﻿SELECT 
	TagId = [Tag].Id,
	Name = [Tag].Name,
	DisplayName = [Tag].Name,
	ParentId = t.[SourceTagId],
	Unit = [Unit].Name
FROM [Tag]
LEFT JOIN [TagRelationships] t ON [Tag].Id = t.[DestinationTagId] AND t.[RelationshipType] = {0} 
LEFT JOIN [Unit] ON [Tag].UnitId = [Unit].Id
